package com.base;

import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 *  ClassName: JDBCOperation
 * Description:
 *
 * @Author liu
 * @Create 2025/4/22 21:56
 * @Version 1.0
 */public class JDBCOperation {

     //单行单例
     @Test
        public void testQuerySingleRowAndColumn() throws Exception {
            //1.注册驱动

            //2.获取连接
            Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu", "root", "15178102072Liu");

            //3.预编译
            PreparedStatement preparedStatement = connection.prepareStatement("SELECT COUNT(*) as count FROM t_emp");

            //执行SQL语句，获取结果
            ResultSet resultSet = preparedStatement.executeQuery();

            //处理结果
            if(resultSet.next()){//输入一个，用if ；多个为 while
                int count = resultSet.getInt("count");
                System.out.println(count);
            }
            //关闭资源
            resultSet.close();
            preparedStatement.close();
            connection.close();
        }


        //单行多列
        @Test
        public void testQuotedSingleRow() throws SQLException {
            //1.注册驱动

            //2.获取连接
            Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu", "root", "15178102072Liu");

            //3.预编译
            PreparedStatement prepareStatement = connection.prepareStatement("SELECT emp_id,emp_name,emp_salary,emp_age FROM t_emp WHERE emp_id = ?");

            //为占位符赋值，结算结果
            prepareStatement.setInt(1,5);
            ResultSet resultSet = prepareStatement.executeQuery();

            //处理结果
            while(resultSet.next()){
                int empId = resultSet.getInt("emp_id");
                String empName = resultSet.getString("emp_name");
                double empSalary = resultSet.getDouble("emp_salary");
                int empAge= resultSet.getInt("emp_age");

                System.out.println(empId+"\t"+empName+"\t"+empSalary+"\t"+empAge);
            }
            //关闭资源
            resultSet.close();
            prepareStatement.close();
            connection.close();
        }


//多行多列
        @Test
        public void testQueryMoreRow() throws SQLException {
            //1.建立连接
            Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu","root","15178102072Liu");

            //2.预编译
            PreparedStatement preparedStatement = connection.prepareStatement("SELECT emp_id,emp_name,emp_salary,emp_age FROM t_emp WHERE emp_age > ?");

            //为占位符赋值
            preparedStatement.setInt(1,25);

            ResultSet resultSet = preparedStatement.executeQuery();

            //处理结果
            while(resultSet.next()){
                int empId = resultSet.getInt("emp_id");
                String empName = resultSet.getString("emp_name");
                double empSalary = resultSet.getDouble("emp_salary");
                int empAge = resultSet.getInt("emp_age");

                System.out.println(empId+"\t"+empName+"\t"+empSalary+"\t"+empAge);
            }

            resultSet.close();
            preparedStatement.close();
            connection.close();

        }


        //插入，新增
      @Test
      public void testInsert() throws SQLException {
           //连接
          Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu", "root", "15178102072Liu");

          //预编译
          PreparedStatement preparedStatement =connection.prepareStatement("INSERT INTO t_emp(emp_name,emp_salary,emp_age) VALUES (?,?,?)");

          //为占位符赋值
          preparedStatement.setString(1,"rose");
          preparedStatement.setDouble(2,555.0);
          preparedStatement.setInt(3,22);

          int result = preparedStatement.executeUpdate();//新增的数据，加入result内

          //运行结果
          //根据受影响，做判断，得出成功或失败
          if(result > 0){
              System.out.println("成功");
          }else{
              System.out.println("失败");
          }

          //关闭资源
          preparedStatement.close();
          connection.close();
      }


      //修改
    @Test
    public void testUpdate() throws SQLException {
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu", "root", "15178102072Liu");

        PreparedStatement preparedStatement = connection.prepareStatement("UPDATE t_emp SET emp_salary =? WHERE emp_id =?");

        preparedStatement.setDouble(1,5550.0);
        preparedStatement.setInt(2,3);

        int result = preparedStatement.executeUpdate();

        if(result >0){
            System.out.println("成功");
        }else{
            System.out.println("失败");
        }

        preparedStatement.close();
        connection.close();

    }

    //删除
    @Test
    public void testDelect() throws SQLException {
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu", "root", "15178102072Liu");

        PreparedStatement preparedStatement = connection.prepareStatement("DELETE FROM t_emp WHERE emp_id = ?");

        preparedStatement.setInt(1,6);

        int result = preparedStatement.executeUpdate();

        if( result >0){
            System.out.println("成功");
        }else{
            System.out.println(" 失败");
        }

        preparedStatement.close();
        connection.close();

    }
    }
